<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html>
<head>
<title>Inserting, updating and deleting data in SQLite</title>
<link rel="stylesheet" href="/cfg/format.css" type="text/css">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta name="keywords" content="SQLite, SQL, database, INSERT, DELETE, UPDATE">
<meta name="description" content="In this part of the SQLite tutorial, we will be
inserting, updating and deleting data.">
<meta name="language" content="en">
<meta name="author" content="Jan Bodnar">
<meta name="distribution" content="global">

<script type="text/javascript" src="/lib/jquery.js"></script>
<script type="text/javascript" src="/lib/common.js"></script>

</head>

<body>

<div class="container2">

<div id="wide_ad" class="ltow">
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* 160x600, August 2011 */
google_ad_slot = "2484182563";
google_ad_width = 160;
google_ad_height = 600;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<div class="content2">


<a href="/" title="Home">Home</a>&nbsp;
<a href="..">Contents</a>


<h1>Inserting, updating and deleting data</h1>


<p>
In this part of the SQLite tutorial, we will insert, update and delete 
data from SQLite tables. We will use the <b>INSERT</b>, 
<b>DELETE</b> and <b>UPDATE</b> statements. 
These statements are part of the SQL Data Manipulation Language, DML.
</p>

<div class="big_hor">
<script type="text/javascript"><!--
google_ad_client = "ca-pub-9706709751191532";
/* big_horizontal */
google_ad_slot = "2904953388";
google_ad_width = 728;
google_ad_height = 90;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>


<h2>Inserting data</h2>

<p>
The INSERT statement is used to insert data into tables. 
We will create a new table, where we will do our examples. 
</p>

<pre class="code">
sqlite> CREATE TABLE Books(Id INTEGER PRIMARY KEY, Title TEXT, Author TEXT, 
   ...> ISBN TEXT DEFAULT 'not available');
</pre>

<p>
We create a new table Books, with Id, Title, Author and ISBN columns. 
</p>

<pre class="code">
sqlite> INSERT INTO Books(Id, Title, Author, ISBN)
   ...> VALUES(1, 'War and Peace', 'Leo Tolstoy', '978-0345472403');
</pre>

<p>
This is the classic INSERT SQL statement. We have specified all column names
after the table name and all values after the VALUES keyword. We add our first 
row into the table. 
</p>

<pre class="code">
sqlite> INSERT INTO Books(Title, Author, ISBN)
   ...> VALUES('The Brothers Karamazov', 'Fyodor Dostoyevsky', '978-0486437910');
</pre>

<p>
We add a new title into the Books table. We have omitted the Id column. 
The Id column is defined as INTEGER PRIMARY KEY. Such columns are auto-incremented in 
SQLite. This means, the SQLite library will add a new Id.
</p>

<pre class="code">
sqlite> SELECT * FROM Books;
Id|Title|Author|ISBN
1|War and Peace|Leo Tolstoy|978-0345472403
2|The Brothers Karamazov|Fyodor Dostoyevsky|978-0486437910
</pre>

<p>
Here is what we have in the Books table. 
</p>

<pre class="code">
sqlite> INSERT INTO Books VALUES(3, 'Crime and Punishment', 'Fyodor Dostoevsky',
   ...> '978-1840224306');
</pre>

<p>
In this SQL statement, we did not specify any column names after the table name. 
In such a case, we have to supply all values.
</p>

<pre class="code">
sqlite> .nullvalue NULL
</pre>

<p>
The <b>.nullvalue</b> command tells the SQLite to show 
NULL values as NULL. SQLite shows empty strings for NULL values by default. 
</p>

<pre class="code">
sqlite> INSERT INTO Books(Id, Title) VALUES(4, 'Paradise Lost');
</pre>

<p>
The INSERT statement omits the last 2 columns. Such columns are filled with the 
default value, or NULL, if there is no default value. The Author column does not 
have a default value, so there is a NULL value. In the CREATE TABLE statement, we 
have specified the ISBN column to have the 'not available' default value.
</p>

<pre class="code">
sqlite> SELECT * FROM Books WHERE Id=4;
Id|Title|Author|ISBN
4|Paradise Lost|NULL|not available
</pre>

<p>
In the third column we have a NULL value. In the fourth one the default 'not available'
string.
</p>


<pre class="code">
sqlite> INSERT INTO Books VALUES(4, 'Paradise Lost', 'John Milton', '978-0486442877');
Error: PRIMARY KEY must be unique
sqlite> INSERT OR REPLACE INTO Books VALUES(4, 'Paradise Lost', 'John Milton', 
   ...> '978-0486442877');
</pre>

<p>
Say we want to put all information into the fourth column. Trying to insert new
data into existing row produces the following error: 'PRIMARY KEY must be unique'. 
In such a case we can use the INSERT OR REPLACE statement. The same could be accomplished 
with the UPDATE statement. 
</p>

<pre class="code">
sqlite> SELECT * FROM Books WHERE Id=4;
Id          Title          Author       ISBN          
----------  -------------  -----------  --------------
4           Paradise Lost  John Milton  978-0486442877
</pre>

<p>
Now we have all information in the fourth row.
</p>

<hr class="btm">

<p>
Since SQLite version 3.7.11 it is possible to insert multiple rows using one
INSERT statement. 
</p>

<pre class="code">
sqlite> CREATE TABLE Ints(Id INTEGER PRIMARY KEY, Val INTEGER);
</pre>

<p>
We will have a simple Ints table to do our work. It will store integers. 
</p>

<pre class="code">
sqlite> INSERT INTO Ints(Val) VALUES (1), (3), (5), (6), (7), (8), (6), (4), (9);
</pre>

<p>
We insert nine rows into the table in one shot. The rows follow the VALUES keyword 
and are separated by a comma character. 
</p>

<pre class="code">
sqlite> SELECT * FROM Ints;
Id          Val       
----------  ----------
1           1         
2           3         
3           5         
4           6         
5           7         
6           8         
7           6         
8           4         
9           9  
</pre>

<p>
These are the contents of the Ints table.
</p>

<hr class="btm">

<p>
We can use the INSERT and SELECT statements together in one statement. 
</p>

<pre class="code">
sqlite> CREATE TABLE Books2(Id INTEGER PRIMARY KEY, Title TEXT, 
   ...> Author TEXT, ISBN TEXT);
</pre>

<p>
First, we create a new table called Books2. Its schema is identical to Books 
table.
</p>

<pre class="code">
sqlite> INSERT INTO Books2 SELECT * FROM Books;
</pre>

<p>
Here we insert all data from the Books table into the Books2 table.
</p>

<pre class="code">
sqlite> SELECT * FROM Books2;
Id|Title|Author|ISBN
1|War and Peace|Leo Tolstoy|978-0345472403
2|The Brothers Karamazov|Fyodor Dostoyevsky|978-0486437910
3|Crime and Punishment|Fyodor Dostoevsky|978-1840224306
4|Paradise Lost|NULL|not available
</pre>

<p>
We verify it. All OK.
</p>


<h2>Deleting data</h2>

<p>
The DELETE keyword is used to delete data from tables. 
First, we are going to delete one row from a table. 
We will use the Books2 table, that we have created previously.
</p>

<pre class="code">
sqlite> DELETE FROM Books2 WHERE Id=1;
</pre>

<p>
We delete a row with Id 1.
</p>

<pre class="code">
sqlite> SELECT * FROM Books2;
Id|Title|Author|ISBN
2|The Brothers Karamazov|Fyodor Dostoyevsky|978-0486437910
3|Crime and Punishment|Fyodor Dostoevsky|978-1840224306
4|Paradise Lost|NULL|not available
</pre>

<p>
We verify that. The first row is missing.
</p>

<pre class="code">
sqlite> DELETE FROM Books2;
</pre>

<p>
This SQL statement deletes all data in the table.
</p>


<h2>Updating data</h2>

<p>
The UPDATE statement is used to change the value
of columns in selected rows of a table.
</p>

<p>
Say we wanted to change 'Leo Tolstoy' to 'Lev Nikolayevich Tolstoy' in our Books table.
The following statement shows, how to accomplish this.
</p>

<pre class="code">
sqlite> UPDATE Books SET Author='Lev Nikolayevich Tolstoy' WHERE Id=1;
</pre>

<p>
The SQL statement sets the author column to 'Lev Nikolayevich Tolstoy'
for the column with id=1.
</p>

<pre class="code">
sqlite> SELECT * FROM Books WHERE Id=1;
Id|Title|Author|ISBN
1|War and Peace|Lev Nikolayevich Tolstoy|978-0345472403
</pre>

<p>
The row is correctly updated. 
</p>

<p>
In this part of the SQLite tutorial, we have inserted, deleted and updated data in 
database tables. 
</p>

<div class="center"> 
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* horizontal */
google_ad_slot = "1734478269";
google_ad_width = 468;
google_ad_height = 60;
//-->
</script> 
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> 
</script> 
</div> 
<br>


<div class="botNav, center">
<span class="botNavItem"><a href="/">Home</a></span> ‡ <span class="botNavItem"><a href="..">Contents</a></span> ‡ 
<span class="botNavItem"><a href="#">Top of Page</a></span>
</div>


<div class="footer">
<div class="signature">
<a href="/">ZetCode</a> last modified January 8, 2013  <span class="copyright">&copy; 2007 - 2013 Jan Bodnar</span>
</div>
</div>

</div> <!-- content -->

</div> <!-- container -->

</body>
</html>

